package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import net.sf.json.JSONObject;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.TgsyDao;

/**
 * 
 * @author zhai
 * @version 创建时间：2015年12月16日 上午9:34:42
 * @功能：推广收益界面
 */
@Repository
public class TgsyDaoImpl extends P2PJdbcDao implements TgsyDao{

	/**
	 * @author zhai
	 * 
	 * 查询个人收益的数据结果
	 */
	@Override
	public List queryTgsyList(JSONObject params) {
		// TODO Auto-generated method stub
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		
		String state=params.optString("state");
		String vipusername=params.optString("vipusername");
		String vipsjhm=params.optString("vipsjhm");
		String tjrusername=params.optString("syrusername");
		String tjrsjhm=params.optString("syrsjhm");
		
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("vipusername", vipusername);
		paramMap.put("vipsjhm", vipsjhm);
		paramMap.put("tjrusername", tjrusername);
		paramMap.put("tjrsjhm", tjrsjhm);
		paramMap.put("state", state);
		
		
		
		
		sql.append(" select ID,VIP_USERNAME,VIP_ZC_SJHM,date_format(CZSJ,'%Y-%m-%d %h:%i:%s') as CZSJ,SYR_USERNAME,SYR_ZC_SJHM,SYFF_STATE,date_format(SYFF_DATE,'%Y-%m-%d %h:%i:%s') as SYFF_DATE from v_p2p_vipcz_syff_info ");
		sql.append(" where 1=1 ");
		
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND SYFF_DATE >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND SYFF_DATE <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(vipusername)){
			sql.append(" AND VIP_USERNAME like "+"'%"+vipusername+"%' ");
		}
		if(StringUtils.isNotEmpty(vipsjhm)){
			sql.append(" AND VIP_ZC_SJHM like "+"'%"+vipsjhm+"%' ");
		}
		if(StringUtils.isNotEmpty(tjrusername)){
			sql.append(" AND SYR_USERNAME like "+"'%"+tjrusername+"%' ");
		}
		if(StringUtils.isNotEmpty(tjrsjhm)){
			sql.append(" AND SYR_ZC_SJHM like "+"'%"+tjrsjhm+"%' ");
		}
		if((StringUtils.isNotEmpty(state))&&(state.equals("00"))){
			System.out.println("查询全部记录。");
		}
		if((StringUtils.isNotEmpty(state))&&(state.equals("01"))){
			sql.append(" AND SYFF_STATE = 01 ");
		}
		if((StringUtils.isNotEmpty(state))&&(state.equals("02"))){
			sql.append(" AND SYFF_STATE = 02 ");
		}
		sql.append(" order by SYFF_DATE desc LIMIT :START,:LIMIT");
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);
	}

	/**
	 * @author zhai
	 * 获取推广收益的数据个数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getTgsyCount(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		
		String state=params.optString("state");
		String vipusername=params.optString("vipusername");
		String vipsjhm=params.optString("vipsjhm");
		String tjrusername=params.optString("syrusername");
		String tjrsjhm=params.optString("syrsjhm");
		
		
		
		StringBuffer sql = new StringBuffer();
		
		
		Map<String, Object> paramMap = new HashMap<String, Object>();
		
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("vipusername", vipusername);
		paramMap.put("vipsjhm", vipsjhm);
		paramMap.put("tjrusername", tjrusername);
		paramMap.put("tjrsjhm", tjrsjhm);
		paramMap.put("state", state);
		
		sql.append(" SELECT count(*) FROM v_p2p_vipcz_syff_info ");
		sql.append(" where 1=1 ");
		
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND SYFF_DATE >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND SYFF_DATE <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(vipusername)){
			sql.append(" AND VIP_USERNAME like "+"'%"+vipusername+"%' ");
		}
		if(StringUtils.isNotEmpty(vipsjhm)){
			sql.append(" AND VIP_ZC_SJHM like "+"'%"+vipsjhm+"%' ");
		}
		if(StringUtils.isNotEmpty(tjrusername)){
			sql.append(" AND SYR_USERNAME like "+"'%"+tjrusername+"%' ");
		}
		if(StringUtils.isNotEmpty(tjrsjhm)){
			sql.append(" AND SYR_ZC_SJHM like "+"'%"+tjrsjhm+"%' ");
		}
		if((StringUtils.isNotEmpty(state))&&(state.equals("00"))){
			System.out.println("查询全部记录。");
		}
		if((StringUtils.isNotEmpty(state))&&(state.equals("01"))){
			sql.append(" AND SYFF_STATE = 01 ");
		}
		if((StringUtils.isNotEmpty(state))&&(state.equals("02"))){
			sql.append(" AND SYFF_STATE = 02 ");
		}
		
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}


}
